# python数据基础第十二节：python与mysql数据库的连接
"""
    python数据基础第十二节：python与mysql数据库的连接



"""

import MySQLdb
import os
import pandas
import matplotlib.pyplot as plt
import matplotlib

connection = MySQLdb.connect(
    host="localhost",
    user="root",
    passwd="root",
    db="python_jichu",
    charset="utf8"
)
# 数据在的主目录，然后递归的读取这个文件夹下面的数据
rootDir = "E:\\Python\\pyspark_demo01\\out_data\\7.1\\"


def exeSQL(sql):
    print("exeSQL:" + sql)
    connection.query(sql)

for fileName in os.listdir(rootDir):
    path = os.path.join(rootDir, fileName)
    # 判断是不是我们需要的文件
    if ".txt" in fileName:
        print(path)
        path = path.replace("\\", "\\\\")
        # 拿到文件名中的时间字段
        datetime = fileName[0:8]
        tableName = "订购明细" + datetime
        # 建表、导入数据到表中
        exeSQL("drop table if exists " + tableName)
        exeSQL(
            "create table " + tableName + "(订单编号 int, 订购日期 datetime, 用户ID int, 产品 char(8), `单价(元)` int, 数量 int, 订购金额 int);");
        # 导入表的语句  测试版本不支持这样导入数据
        # exeSQL(
        #     "LOAD DATA LOCAL INFILE '" + path + "' INTO TABLE `" + tableName + "` CHARACTER SET 'UTF8' COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\\n' IGNORE 1 ROWS;")
        # 更换插入数据的方式
        lines = open(path, 'r', encoding='utf-8')
        for line in lines:
            linelist = line.split(",")
            sql = "insert into %s VALUES ('%s','%s','%s','%s','%s','%s','%s')" % (
            tableName, linelist[0], str(linelist[1]), linelist[2], linelist[3], linelist[4], linelist[5],
            linelist[6].replace("\n", ""))
            exeSQL(sql)

        # 建立日订购统计表
        exeSQL("create table if not exists 日订购统计表(统计日期 date, 订购用户数 int, 订购次数 int, 人均订购金额 double, 订购总额 double);")
        exeSQL("delete from 日订购统计表 where 统计日期='" + datetime + "';")
        # mysql的统计语句
        exeSQL(
            "insert into 日订购统计表 select '" + datetime + "', count(distinct 用户ID), count(用户ID), sum(订购金额)/count(distinct 用户ID), sum(订购金额) from `" + tableName + "`;")

# 读取数据sql语句
statDay = pandas.read_sql('select * from 日订购统计表;', con=connection)
# 记得用完要关闭连接
connection.close()

# 绘图
font = {
    'family': 'SimHei'
}

matplotlib.rc('font', **font)

plot1 = plt.plot(statDay['统计日期'], statDay['订购用户数'])
plot2 = plt.plot(statDay['统计日期'], statDay['订购次数'])
plot3 = plt.plot(statDay['统计日期'], statDay['人均订购金额'])
plt.show()
# 图例
plt.legend(('订购用户数', '订购次数', '人均订购金额'))
